package com.company.ems.dao;

import com.company.ems.model.Employee;
import com.company.ems.util.DatabaseUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDAOImpl {

    // 获取所有员工
    public List<Employee> getAllEmployees() {
        List<Employee> employees = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            String sql = "SELECT * FROM employees ORDER BY id DESC";
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();

            while (rs.next()) {
                Employee emp = new Employee();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setDepartment(rs.getString("department"));
                emp.setPosition(rs.getString("position"));
                emp.setSalary(rs.getBigDecimal("salary"));
                emp.setEmail(rs.getString("email"));
                emp.setPhone(rs.getString("phone"));
                emp.setHireDate(rs.getDate("hire_date"));
                emp.setCreatedBy(rs.getInt("created_by"));
                emp.setCreatedAt(rs.getTimestamp("created_at"));
                emp.setUpdatedAt(rs.getTimestamp("updated_at"));
                employees.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                DatabaseUtil.closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return employees;
    }

    // 根据ID获取员工
    public Employee getEmployeeById(int id) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            String sql = "SELECT * FROM employees WHERE id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();

            if (rs.next()) {
                Employee emp = new Employee();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setDepartment(rs.getString("department"));
                emp.setPosition(rs.getString("position"));
                emp.setSalary(rs.getBigDecimal("salary"));
                emp.setEmail(rs.getString("email"));
                emp.setPhone(rs.getString("phone"));
                emp.setHireDate(rs.getDate("hire_date"));
                emp.setCreatedBy(rs.getInt("created_by"));
                emp.setCreatedAt(rs.getTimestamp("created_at"));
                emp.setUpdatedAt(rs.getTimestamp("updated_at"));
                return emp;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                DatabaseUtil.closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    // 添加员工
    public boolean addEmployee(Employee employee) {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            String sql = "INSERT INTO employees (name, department, position, salary, email, phone, hire_date, created_by) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, employee.getName());
            stmt.setString(2, employee.getDepartment());
            stmt.setString(3, employee.getPosition());
            stmt.setBigDecimal(4, employee.getSalary());
            stmt.setString(5, employee.getEmail());
            stmt.setString(6, employee.getPhone());
            stmt.setDate(7, new java.sql.Date(employee.getHireDate().getTime()));
            stmt.setInt(8, employee.getCreatedBy());

            int rows = stmt.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
                DatabaseUtil.closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    // 更新员工
    public boolean updateEmployee(Employee employee) {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            String sql = "UPDATE employees SET name=?, department=?, position=?, salary=?, email=?, phone=?, hire_date=? " +
                    "WHERE id=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, employee.getName());
            stmt.setString(2, employee.getDepartment());
            stmt.setString(3, employee.getPosition());
            stmt.setBigDecimal(4, employee.getSalary());
            stmt.setString(5, employee.getEmail());
            stmt.setString(6, employee.getPhone());
            stmt.setDate(7, new java.sql.Date(employee.getHireDate().getTime()));
            stmt.setInt(8, employee.getId());

            int rows = stmt.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
                DatabaseUtil.closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    // 删除员工
    public boolean deleteEmployee(int id) {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DatabaseUtil.getConnection();
            String sql = "DELETE FROM employees WHERE id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);

            int rows = stmt.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
                DatabaseUtil.closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    // 多条件查询员工（可选功能）
    public List<Employee> searchEmployees(String name, String department, String position) {
        List<Employee> employees = new ArrayList<>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            StringBuilder sql = new StringBuilder("SELECT * FROM employees WHERE 1=1");

            if (name != null && !name.isEmpty()) {
                sql.append(" AND name LIKE ?");
            }
            if (department != null && !department.isEmpty()) {
                sql.append(" AND department = ?");
            }
            if (position != null && !position.isEmpty()) {
                sql.append(" AND position = ?");
            }

            stmt = conn.prepareStatement(sql.toString());
            int paramIndex = 1;

            if (name != null && !name.isEmpty()) {
                stmt.setString(paramIndex++, "%" + name + "%");
            }
            if (department != null && !department.isEmpty()) {
                stmt.setString(paramIndex++, department);
            }
            if (position != null && !position.isEmpty()) {
                stmt.setString(paramIndex++, position);
            }

            rs = stmt.executeQuery();

            while (rs.next()) {
                Employee emp = new Employee();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setDepartment(rs.getString("department"));
                emp.setPosition(rs.getString("position"));
                emp.setSalary(rs.getBigDecimal("salary"));
                emp.setEmail(rs.getString("email"));
                emp.setPhone(rs.getString("phone"));
                emp.setHireDate(rs.getDate("hire_date"));
                emp.setCreatedBy(rs.getInt("created_by"));
                emp.setCreatedAt(rs.getTimestamp("created_at"));
                emp.setUpdatedAt(rs.getTimestamp("updated_at"));
                employees.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                DatabaseUtil.closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return employees;
    }
}